package com.scuec.groupfive.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import java.util.List;
import java.util.Map;

@Mapper
public interface AnswerStatisticsMapper {

    /**
     * 统计三门科目的答题比例
     * @return 包含科目名称、答题数量和百分比的列表
     */
    @Select("SELECT s.subject_name AS subjectName, " +
            "COUNT(*) AS answerCount, " +
            "ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM answer_records ar JOIN questions q ON ar.question_id = q.question_id WHERE q.subject_id IN (SELECT subject_id FROM subjects)), 2) AS percentage " +
            "FROM answer_records ar " +
            "JOIN questions q ON ar.question_id = q.question_id " +
            "JOIN subjects s ON q.subject_id = s.subject_id " +
            "WHERE s.subject_id IN (SELECT subject_id FROM subjects) " +
            "GROUP BY s.subject_id, s.subject_name " +
            "ORDER BY COUNT(*) DESC")
    List<Map<String, Object>> getSubjectAnswerStatistics();
}